package com.info.zhiduoduo.repository.mapper.partner;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.evaluate.EvaluateNoticeInfoDto;
import com.info.zhiduoduo.repository.dto.marketingcooperative.MarketingCooperativePartnerDto;
import com.info.zhiduoduo.repository.dto.partner.PartnerDetailDto;
import com.info.zhiduoduo.repository.dto.partner.PartnerNameDto;
import com.info.zhiduoduo.repository.entity.partner.Partner;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

/**
 * <p>
 * 合伙人基本信息表 Mapper 接口
 * </p>
 *
 * @author bob
 * @since 2019-06-17
 */
public interface PartnerMapper extends BaseMapper<Partner> {

    @Select("select * from zdd_partner where phone = #{phone}")
    Partner checkPartnerExistByPhone(@Param("phone") String phone);

    @Select(" select p.partnerid,p.nickname,p.avatar,p.phone,p.state,pa.realname_auth as authState," +
            " if(IFNULL(pb.acctno,'')='',0,1) as hasBankcard," +
            " (select ar.reason from zdd_partner_auth_record ar " +
            " where ar.acctid = pa.acctid order by ar.id desc limit 1) as authReason" +
            " from zdd_partner p " +
            " left join zdd_partner_account pa on pa.partnerid = p.partnerid " +
            " left join zdd_partner_bankcard pb on pb.partnerid = p.partnerid and pb.state = 1 " +
            " where openid = #{openid} " +
            " and p.deleted = 0 ")
    Partner getPartnerByOpenid(@Param("openid") String openid);

    @Select(" select p.*,pa.acctid,pa.promo_code,pa.realname_auth realnameAuth,pa.realname,pa.effectivedate,pa.expiredate," +
            " pa.identity_type,pa.idno,pa.idfrontimg,pa.idbackimg,pa.work_company as workCompany," +
            " pa.wechat_number as wechatNumber,pa.province,pa.city,pa.email,pa.task,pa.studio_id,pa.whether_crowd_partner," +
            " pa.crowd_cost_state,pa.crowd_cost_primary_rate,pa.crowd_cost_secondary_rate,pa.crowd_cost_secondary_amount, " +
            " pa.industry,pa.occupied_time,pa.area,pa.resource,pa.scale,pa.partner_type,pa.remit_type,pa.partner_remark,pa.extra_info," +
            " pa2.realname as inviter,pb.payername,pb.cardtype,pb.bankname,pb.open_acct_bank_name,pb.telephone,pb.acctno,pb.idno as acctIdno," +
            " bm.business_manager_id as businessManagerId,bm.business_manager as businessManager,bm.nickname as businessNickname," +
            " bm.phone as businessPhone,ms.username as userName,pa.vaccine_compid,c.compname as vaccineCompname,pa.vaccine_poster," +
            " pa.vaccine_flag " +
            " from zdd_partner p " +
            " left join zdd_partner_account pa on pa.partnerid = p.partnerid " +
            " left join zdd_partner_bankcard pb on pb.partnerid = p.partnerid and pb.state = 1 " +
            " left join zdd_partner_account pa2 on pa2.promo_code = p.invite_code and p.invite_code != '' " +
            " left join zdd_business_manager bm on bm.business_manager_id = pa.business_manager_id " +
            " left join zdd_maker_studio ms on ms.userid = pa.studio_id " +
            " LEFT JOIN zdd_company c on c.compid = pa.vaccine_compid " +
            " where p.partnerid = #{partnerid} " +
            " and p.deleted = 0 ")
    PartnerDetailDto getPartnerDetail(@Param("partnerid") int partnerid);

    @Delete(" delete p,pa,paf,par,pc,psi,psu " +
            " from zdd_partner p " +
            " left join zdd_partner_account pa on pa.partnerid=p.partnerid " +
            " left join zdd_partner_accountflow paf on paf.partnerid=p.partnerid " +
            " left join zdd_partner_auth_record par on par.acctid=pa.acctid " +
            " left join zdd_partner_company pc on pc.partnerid=p.partnerid " +
            " left join zdd_partner_share_img psi on psi.partnerid=p.partnerid " +
            " left join zdd_partner_share_user psu on psu.partnerid=p.partnerid" +
            " where p.partnerid=#{partnerid} ")
    int delPartner(@Param("partnerid") Integer partnerid);

    @Select(" select openid from zdd_partner p " +
            " join zdd_partner_account pa on pa.partnerid = p.partnerid " +
            " where p.state = 2 and pa.realname_auth = 4 " +
            " and p.deleted = 0 and p.notice_flag = 0 ")
    List<String> getAllValidOpenids();

    @Select(" select p.partnerid AS userId, p.openid AS openId, 3 AS userType from zdd_partner p " +
            " join zdd_partner_account pa on pa.partnerid = p.partnerid " +
            " where p.state in (2,3,4) and pa.realname_auth = 4 " +
            " and p.deleted = 0 ")
    List<EvaluateNoticeInfoDto> getPushEvaluateNoticeNeedData();

    @Select(" select openid from zdd_partner p " +
            " join zdd_partner_account pa on pa.partnerid = p.partnerid " +
            " where p.state = 2 and pa.realname_auth = 4 " +
            " and p.deleted = 0 and p.notice_flag = 0 " +
            " and if(#{provinceid}=0, 1=1, pa.province=#{provinceid}) " +
            " and if(#{cityid}=0, 1=1, pa.city=#{cityid})")
    List<String> getValidOpenidsByProvinceAndCity(int provinceid, int cityid);

    @Select(" select p.*,pa.acctid,pa.promo_code,pa.realname_auth realnameAuth,pa.realname,pa.effectivedate,pa.expiredate," +
            " pa.identity_type,pa.idno,pa.idfrontimg,pa.idbackimg,pa.work_company as workCompany," +
            " pa.wechat_number as wechatNumber,pa.province,pa.city,pa.email,pa.task,pa.studio_id, " +
            " pa.crowd_cost_state,pa.crowd_cost_primary_rate,pa.crowd_cost_secondary_rate,pa.crowd_cost_secondary_amount, " +
            " pa.industry,pa.occupied_time,pa.area,pa.resource,pa.scale,pa.partner_type,pa.remit_type,pa.partner_remark,pa.extra_info," +
            " pa2.realname as inviter,pb.payername,pb.cardtype,pb.bankname,pb.open_acct_bank_name,pb.telephone,pb.acctno,pb.idno as acctIdno," +
            " bm.business_manager_id as businessManagerId,bm.business_manager as businessManager,bm.nickname as businessNickname," +
            " bm.phone as businessPhone,pa.vaccine_compid,c.compname as vaccineCompname,pa.vaccine_poster,pa.vaccine_flag  " +
            " from zdd_partner p " +
            " join zdd_partner_account pa on pa.partnerid = p.partnerid " +
            " left join zdd_partner_bankcard pb on pb.partnerid = p.partnerid and pb.state = 1 " +
            " left join zdd_partner_account pa2 on pa2.promo_code = p.invite_code and p.invite_code != '' " +
            " left join zdd_business_manager bm on bm.business_manager_id = pa.business_manager_id " +
            " LEFT JOIN zdd_company c on c.compid = pa.vaccine_compid " +
            " where p.state = 2 and pa.realname_auth = 4 " +
            " and DATE(pa.effectivedate) = DATE(#{date}) " +
            " and p.deleted = 0 ")
    List<PartnerDetailDto> getPartnerDetailByEffectivedate(@Param("date") Date date);

    @Select(" select p.*,pa.acctid,pa.promo_code,pa.realname_auth realnameAuth,pa.realname,pa.effectivedate,pa.expiredate," +
            " pa.identity_type,pa.idno,pa.idfrontimg,pa.idbackimg,pa.work_company as workCompany," +
            " pa.wechat_number as wechatNumber,pa.province,pa.city,pa.email,pa.task,pa.studio_id, " +
            " pa.crowd_cost_state,pa.crowd_cost_primary_rate,pa.crowd_cost_secondary_rate,pa.crowd_cost_secondary_amount, " +
            " pa.industry,pa.occupied_time,pa.area,pa.resource,pa.scale,pa.partner_type,pa.remit_type,pa.partner_remark,pa.extra_info," +
            " pa2.realname as inviter,pb.payername,pb.cardtype,pb.bankname,pb.open_acct_bank_name,pb.telephone,pb.acctno,pb.idno as acctIdno," +
            " bm.business_manager_id as businessManagerId,bm.business_manager as businessManager,bm.nickname as businessNickname," +
            " bm.phone as businessPhone,pa.vaccine_compid,c.compname as vaccineCompname,pa.vaccine_poster,pa.vaccine_flag  " +
            " from zdd_partner p " +
            " join zdd_partner_account pa on pa.partnerid = p.partnerid " +
            " left join zdd_partner_bankcard pb on pb.partnerid = p.partnerid and pb.state = 1 " +
            " left join zdd_partner_account pa2 on pa2.promo_code = p.invite_code and p.invite_code != '' " +
            " left join zdd_business_manager bm on bm.business_manager_id = pa.business_manager_id " +
            " LEFT JOIN zdd_company c on c.compid = pa.vaccine_compid " +
            " where p.state = 2 and pa.realname_auth = 4 " +
            " and CURDATE() between pa.effectivedate and pa.expiredate " +
            " and p.deleted = 0 ")
    List<PartnerDetailDto> getAllValidPartner();

    @Select(" select pa.partnerid,pa.realname " +
            " from zdd_partner_account pa " +
            " where pa.realname like '%${name}%' " +
            " and pa.realname_auth = 4 " +
            " and not exists(select * from zdd_partner_sub ps where ps.sub_partnerid = pa.partnerid and ps.deleted = 0) ")
	List<PartnerNameDto> findValidSubPartnerList(@Param("name") String name);

    @Select(" select pa.partnerid,pa.realname " +
            " from zdd_partner_account pa " +
            " where pa.realname like '%${name}%' " +
            " and pa.realname_auth = 4 " +
            " and pa.crowd_cost_state = 2 " +
            " and not exists(select * from zdd_partner_crowdsource_sub ps where ps.sub_partnerid = pa.partnerid and ps.deleted = 0) " +
            " order by pa.acctid desc ")
	List<PartnerNameDto> findValidCrowdsourceSubPartnerList(@Param("name") String name);

    @Select(" select pa.partnerid,pa.realname " +
            " from zdd_partner_account pa " +
            " where pa.realname like '%${name}%' " +
            " and pa.realname_auth = 4 " +
            " and pa.crowd_cost_state = 2 " +
            " order by pa.acctid desc ")
	List<PartnerNameDto> findValidCrowdsourcePartnerList(@Param("name") String name);

    @Select(" select * from zdd_partner where loginname = #{loginname}")
	Partner findByLoginname(String loginname);

    @Select(" select COUNT(1) from zdd_partner where loginname = #{loginname}")
	int existsLoginname(String loginname);

    @Select("SELECT " +
            " mc.id as id, " +
            " mc.partnerid as partnerid, " +
            " paa.realname as partnerName, " +
            " mc.compid as compid, " +
            " mcc.compname as compname, " +
            " paa.partnerid as inviter, " +
            " paa.realname as inviterName, " +
            " mc.state as state, " +
            " mc.createtime as createtime  " +
            "FROM " +
            " zdd_marketing_cooperative mc " +
            " LEFT JOIN zdd_company mcc ON mcc.compid = mc.compid  " +
            " LEFT JOIN zdd_partner_account paa on mc.partnerid = paa.partnerid " +
            "WHERE mc.partnerid = #{partnerid}")
    List<MarketingCooperativePartnerDto> getMarketingCooperativeList(@Param("partnerid") int partnerid);

    @Select(" select p.openid from zdd_partner p " +
            " where p.partnerid = #{partnerId} ")
    String getValidOpenId(@Param("partnerId") int partnerId);
}
